import mysql.connector
from mysql.connector import Error
from configure.configure import settings



def create_db_connection():
    """创建数据库连接"""
    connection = None
    try:

        connection = mysql.connector.connect(
            host=settings.DB_HOST,
            user=settings.DB_USER,
            password=settings.DB_PASSWORD,
            database=settings.DB_NAME,
            # 关键：使用 init_command 参数设置时区
            init_command='SET SESSION time_zone = "+8:00"'
        )
    except Error as err:
        print(f"错误: '{err}'")
    return connection


def read_query(connection, query, params=None):
    """执行读取查询（SELECT）并返回结果"""
    cursor = connection.cursor(dictionary=True)
    results = None
    try:
        cursor.execute(query, params or ())
        results = cursor.fetchall()
        # 打印实际执行的SQL语句（带参数替换）
        print(f"🚀 [EXECUTED SQL]: {cursor.statement}")
        return results
    except Error as err:
        print(f"❌ [SQL ERROR]: '{err}'")
        print(f"🚀 [FAILED SQL]: {query}")
        print(f"📊 [FAILED PARAMS]: {params}")
        return []
    finally:
        cursor.close()


def execute_query(connection, query, params=None):
    """执行查询语句（INSERT, UPDATE, DELETE 等）"""
    cursor = connection.cursor()
    try:
        cursor.execute(query, params or ())
        connection.commit()
        # 打印实际执行的SQL语句（带参数替换）
        print(f"🚀 [EXECUTED SQL]: {cursor.statement}")
        return cursor.lastrowid
    except Error as err:
        print(f"❌ [SQL ERROR]: '{err}'")
        print(f"🚀 [FAILED SQL]: {query}")
        print(f"📊 [FAILED PARAMS]: {params}")
        connection.rollback()
        return None
    finally:
        cursor.close()


# 是否是执行查询操作  查询 true  其他操作 false
def main_opera(sql_item: str, item=None, type_opera: bool = False):
    # 打印SQL语句用于调试
    print("🔍 [SQL DEBUG] 执行SQL语句:")
    print(f"📝 SQL: {sql_item}")
    if item:
        print(f"📊 参数: {item}")
    print("-" * 80)
    
    # 连接数据库
    connection = create_db_connection()
    if not connection:
        return "数据库连接失败"

    try:
        if type_opera:
            results = read_query(connection, sql_item, item)
            print(f"✅ [SQL RESULT] 查询结果数量: {len(results) if results else 0}")
            return results
        else:
            results = execute_query(connection, sql_item, item)
            print(f"✅ [SQL RESULT] 影响行数/插入ID: {results}")
            return results

    finally:
        # 关闭数据库连接
        if connection.is_connected():
            connection.close()
